package com.wanlian.dao.impl;

import com.wanlian.common.PageCondition;
import com.wanlian.dao.UserDao;
import com.wanlian.entity.User;
import com.wanlian.model.ResultModel;
import com.wanlian.sql.SqlUtil;
import com.wanlian.util.DateUtil;
import com.wanlian.util.StringUtil;
import com.wanlian.vo.Page;
import org.springframework.stereotype.Component;

import java.sql.Timestamp;
import java.text.ParseException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.Map;

@Component
public class UserDaoImpl extends GenericDaoImpl<User> implements UserDao {
    public UserDaoImpl() {
        super(User.class);
    }


    @Override
    public ResultModel queryUsers(PageCondition pageCondition) {
        String[] column = {"id", "headimg", "username", "phone", "isEnable", "lastLoginTime", "lastUpdateTime", "dianxin", "intscore", "totalscore"};
        String sql = "select u.id,u.headimg,u.username,u.phone,u.isenable,u.lastLoginTime,u.lastUpdateTime,sd.dianxin,si.intscore,sto.totalscore from tb_user u" +
                " left JOIN " +
                " (select u.id as userid,sum(s.score) as dianxin from tb_user u left join tb_score s on u.id=s.userid where s.rankType=1 group by s.userid) sd" +
                " on u.id=sd.userid " +
                " left join " +
                " (select u.id as userid,sum(s.score) as intscore from tb_user u left join tb_score s on u.id=s.userid where s.rankType=2 group by s.userid) si" +
                " on u.id=si.userid " +
                " left join" +
                " (select s.userid as userid,sum(s.score) as totalscore from tb_user u left join tb_score s on u.id=s.userid where 1=1 group by s.userid) sto" +
                " on u.id=sto.userid";
        StringBuilder sqlWhere = new StringBuilder(" where 1=1 ");
        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isExistByKey("phone")) {
            String phone = (String) pageCondition.getConditions().get("phone");
            if (StringUtil.hasValue(phone)) {
                sqlWhere.append(" and phone like ? ");
                param.add("%" + phone + "%");
            }
        }
        if (pageCondition.isExistByKey("startTime")) {
            String startTime = (String) pageCondition.getConditions().get("startTime");
            if (StringUtil.hasValue(startTime)) {
                sqlWhere.append(" and lastUpdateTime >= ? ");
                Timestamp ts = Timestamp.valueOf(startTime + " 00:00:00.00");
                param.add(ts);
            }
        }
        if (pageCondition.isExistByKey("endTime")) {
            String endTime = (String) pageCondition.getConditions().get("endTime");
            if (StringUtil.hasValue(endTime)) {
                sqlWhere.append(" and lastUpdateTime <= ? ");
                Timestamp ts = Timestamp.valueOf(endTime + " 23:59:59.00");
                param.add(ts);
            }
        }
        if (pageCondition.isExistByKey("isEnable")) {
            if (pageCondition.getConditions().get("isEnable") != null) {
                sqlWhere.append(" and isenable = ? ");
                param.add((int) pageCondition.getConditions().get("isEnable"));
            }
        }

        if (pageCondition.isExistByKey("management")) {
            if (pageCondition.getConditions().get("management") != null) {
                sqlWhere.append(" and management = ? ");
                param.add((int) pageCondition.getConditions().get("management"));
            }
        }

        if (pageCondition.isExistByKey("username")) {
            String username = (String) pageCondition.getConditions().get("username");
            if (StringUtil.hasValue(username)) {
                sqlWhere.append("and username like ? ");
                param.add("%" + username + "%");
            }
        }


        List<Map<String, Object>> lists = new ArrayList<>();
        lists = this.queryForListObjectByPage(sql + sqlWhere + " order by u.isenable desc ", param.toArray(), pageCondition.getPage(), column);
        int count = queryCountByParam("select count(1) from tb_user u left JOIN\n" +
                "(select u.id as userid,sum(s.score) as dianxin from tb_user u left join tb_score s on u.id=s.userid where s.rankType=1 group by s.userid) sd \n" +
                "on u.id=sd.userid\n" +
                "left join (select u.id as userid,sum(s.score) as intscore from tb_user u left join tb_score s on u.id=s.userid where s.rankType=2 group by s.userid) si\n" +
                "on u.id=si.userid" + sqlWhere, param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }

    @Override
    public List<User> queryUsersByName(Page page, String name) {
        List<User> lists = new ArrayList<User>();
        String like = SqlUtil.getLikeCondition(name);
        String hql = String.format("from %s where username like ?", entityClassName());
        lists = this.queryForList(hql, new Object[]{like});
        return lists;
    }

    @Override
    public List<User> queryUsers() {
        List<User> lists = new ArrayList<User>();
        String hql = String.format("from %s where  isenable = ? ", entityClassName());
        lists = this.queryForList(hql, new Object[]{true});
        return lists;
    }


    @Override
    public List<User> queryUsersByName(String name) {
        List<User> lists = new ArrayList<User>();
        String hql = String.format("from %s where username like %?%", entityClassName());
        lists = this.queryForList(hql, new Object[]{name});
        return lists;
    }

    @Override
    public List<User> queryUsersByPhone(String phone) {
        List<User> lists = new ArrayList<User>();
        String hql = String.format("from %s where phone=?", entityClassName());
        lists = this.queryForList(hql, new Object[]{phone});
        return lists;
    }

    @Override
    public User queryUserByloginInfo(String phone, String password) {
        String hql = String.format("from %s where phone=? and upassword=?", entityClassName());
        User user = this.queryForObject(hql, new Object[]{phone, password});
        return user;
    }

    /**
     * 模糊查询用户
     *
     * @param phoneOrName
     * @param page
     * @return
     */
    @Override
    public ResultModel queryByPartName(String phoneOrName, Page page) {
        String hql = String.format("from %s where   (phone like ? or username like ?) order by phone", entityClassName());
        List<User> users = this.queryForListByPage(hql, new Object[]{"%" + phoneOrName + "%", "%" + phoneOrName + "%"}, page);
        ResultModel resultModel = new ResultModel(true, users);
        return resultModel;
    }

    /**
     * 通过电话号码获取用户
     *
     * @param phone
     * @return
     */
    @Override
    public User queryUserByPhone(String phone) {
        String hql = String.format("from %s where phone=?", entityClassName());
        User user = this.queryForObject(hql, new Object[]{phone});
        return user;
    }

    @Override
    public ResultModel queryByEnable(Page page, boolean isenable) {
        StringBuilder sqlWhere = new StringBuilder(" where isenable = ? ");
        ArrayList<Object> param = new ArrayList<Object>();
        param.add(isenable);
        List<User> lists = new ArrayList<>();
        String hql = String.format("from %s " + sqlWhere, entityClassName());
        System.out.println(hql);
        lists = this.queryForListByPage(hql, param.toArray(), page);
        String countSql = String.format("select count(*) from %s" + sqlWhere, entityClassName());
        long count = this.queryCount(countSql, param.toArray());
        ResultModel resultModel = new ResultModel(true, lists, count);
        return resultModel;
    }

    @Override
    public void doUpdateLoginTime(User user) {
        user.setLastLoginTime(DateUtil.currentTimestamp());
        this.update(user);
    }

    @Override
    public List<User> queryUsersByParam(PageCondition pageCondition) {
        StringBuilder sqlWhere = new StringBuilder(" where 1=1 ");
        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isEmpty("userId")) {
            int userId = Integer.parseInt((String) pageCondition.getByKey("userId"));
            sqlWhere.append(" and id=? ");
            param.add(userId);
        }

        List<User> lists = new ArrayList<User>();
        String hql = String.format("from %s " + sqlWhere, entityClassName());
        lists = this.queryForListByPage(hql, param.toArray(), pageCondition.getPage());
        return lists;
    }


    @Override
    public User queryintAccountByAccount(String intAccount) {
        String hql = String.format("from %s where intaccount=? ", entityClassName());
        User user = this.queryForObject(hql, new Object[]{intAccount});
        return user;
    }

    @Override
    public long queryUserCountToday() {

        try {
            Date dateNow = DateUtil.currentDate();
            String today = DateUtil.formatDateNoHour(dateNow);
            String countsql = String.format("select count(*) from %s where lastupdatetime> date(?) ", entityClassName());
            long count = this.queryCount(countsql, new Object[]{today});
            return count;
        } catch (ParseException e) {
            e.printStackTrace();
        }
        return 0;
    }


    @Override
    public User queryUserByTag(String userTag) {
        String hql = String.format("from %s where usertag=? ", entityClassName());
        User user = this.queryForObject(hql, new Object[]{userTag});
        return user;
    }

    @Override
    public ResultModel queryIntDetailList(PageCondition pageCondition) {
        ResultModel resultModel=new ResultModel(true);
        String sql="  select icd.wallet,icd.intnum,icd.realarrival,icd.gas,icd.realgas,icd.ethtx,icd.picktime,icd.status,u.username,u.phone,u.headimg " +
                " from tb_intchangedetail icd,tb_scorehistory sh,tb_user u where icd.shid=sh.id and sh.userid=u.id";
        StringBuilder sqlWhere=new StringBuilder();
        String[] column = {"wallet","intnum","realarrival", "gas", "realgas", "ethtx", "picktime","status","username","phone","headimg"};
        ArrayList<Object> param = new ArrayList<Object>();
        if (pageCondition.isEmpty("phone")) {
            String phone = (String) pageCondition.getByKey("phone");
            sqlWhere.append(" and u.phone like ?");
            param.add("%" + phone + "%");
        }
        if (pageCondition.isEmpty("wallet")) {
            String wallet = (String) pageCondition.getByKey("wallet");
            sqlWhere.append(" and icd.wallet like ?");
            param.add("%" + wallet + "%");
        }
        if (pageCondition.isEmpty("ethtx")) {
            String ethtx = (String) pageCondition.getByKey("ethtx");
            sqlWhere.append(" and icd.ethtx like ?");
            param.add("%" + ethtx + "%");
        }

        if (pageCondition.isEmpty("status")) {
            int status = Integer.parseInt(""+ pageCondition.getByKey("status"));
            sqlWhere.append(" and icd.status = ?");
            param.add( status );
        }
        sqlWhere.append(" order by icd.picktime desc ");
        List<Map<String, Object>> lists = this.queryForListObjectByPage(sql+sqlWhere, param.toArray(), pageCondition.getPage(), column);
        System.out.println(sql+sqlWhere);
        int count = this.queryCountByParam("select count(1) from tb_intchangedetail icd,tb_scorehistory sh,tb_user u where icd.shid=sh.id and sh.userid=u.id "+sqlWhere, param.toArray());
        resultModel.setObject(lists);
        resultModel.setCount(count);
        return resultModel;
    }

    @Override
    public List<User> queryAllEnableUser() {
        List<User> lists = new ArrayList<User>();
        String hql = String.format("from %s where  isenable = ? ", entityClassName());
        lists = this.queryForList(hql, new Object[]{true});
        return lists;
    }
}
